T-SQL : Limited Conditions & Trigger

Entity Integrality

All in all, there should be a ‘Primary Key’ to be a kind of ‘Index’ to the tuple(row) it belongs to, and we can refer to specific tuple(row) by checking primary key. Primary is unique for its tuple, and it should represent this tuple very well.We call those columns which can be a primary key ‘Candidate Key’. A ‘Key’ also could be a combination of several columns. A row can only have one Primary key, but it can hold many candidate key.

If a tuple have a column name, which is a primary key of another tuple, it will be named as ‘Foreign Key’. Foreign is a reminder saying :“I am a Primary Key somewhere!”

It’s easy to set these keys when establishing tables or we can alter columns after building the table.

add constraint pk_columnName primary key(columnName)

Here the pk_columnName is a name of an entity, for the ‘keys’ will be saved as entities in SQL server. Because there’re other type that will be also saved as entities, we use Hungarian Notation to add a pk_ in front of it. Here’s another example of adding a foreign key.

alter table tablename
add constraint fk_columnName
foreign key(columnName) references TableName(ColumnName)

Reference Integrality

Some times, we can add custom rules to limit the value of a column.

//TODO

Trigger

A trigger is just like a IFTTT , which contains 2 part: Condition and Function. The former decide when the trigger is activated, and the latter decide what the trigger will do when activated.

For the condition one, we have 3 options : delete, alter and insert, all in literal meanings. And the trigger’s format is basically like this:

create trigger triggername on tablename for delete/alter/insert
as
begin
	--what the trigger will do (you can declare something, select something and update/delete/insert)
end

when you use trigger, actually there’re some tables for you :

inserted , deleted and altered , which hold the changed data.